package com.kb.erp.mapper.sqlServer;

import com.baomidou.dynamic.datasource.annotation.DS;
import com.kb.erp.entity.pcbout.MonthUse;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;
import org.springframework.stereotype.Component;

import java.util.List;

@Component
@Mapper


@DS("k3")
public interface K3Mapper {

    @Select({
            "<script>",
            "<![CDATA[",
            "WITH PickMtrlData AS (",
            "    SELECT ",
            "        t2.FWORKSHOPID, ",
            "        t1.FMATERIALID,",
            "        SUM(t1.FACTUALQTY) AS TotalFACTUALQTY",
            "    FROM ",
            "        T_SP_PICKMTRLDATA t1",
            "    JOIN ",
            "        T_SP_PICKMTRL t2 ON t1.FID = t2.FID",
            "    WHERE ",
            "        t2.FDATE >= DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - 2, 0) ",
            "        AND t2.FDATE < DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0)",
            "    GROUP BY",
            "        t2.FWORKSHOPID, ",
            "        t1.FMATERIALID",
            "),",
            "MisDeliveryData AS (",
            "    SELECT ",
            "        t1.FMATERIALID,",
            "        SUM(t1.FQTY) AS TotalFACTUALQTY,",
            "  t2.FDEPTID as FWORKSHOPID",
            "    FROM ",
            "        T_STK_MISDELIVERYENTRY t1",
            "    JOIN ",
            "        T_STK_MISDELIVERY t2 ON t1.FID = t2.FID",
            "    WHERE ",
            "        t2.FDATE >= DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - 2, 0) ",
            "        AND t2.FDATE < DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0)",
            "    GROUP BY",
            "        t1.FMATERIALID,",
            "  t2.FDEPTID",
            "),",
            "ReturnMtrlData AS (",
            "    SELECT ",
            "        t1.FMATERIALID,",
            "        SUM(t1.FQTY) AS TotalFACTUALQTY,",
            " t2.FWORKSHOPID",
            "    FROM ",
            "        T_SP_RETURNMTRLENTRY t1",
            "    JOIN ",
            "        T_SP_RETURNMTRL t2 ON t1.FID = t2.FID",
            "    WHERE ",
            "        t2.FDATE >= DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - 2, 0) ",
            "        AND t2.FDATE < DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0)",
            "    GROUP BY",
            "        t1.FMATERIALID,",
            "   t2.FWORKSHOPID",
            ")",
            "SELECT ",
            "    p.FWORKSHOPID AS workshop,",
            "    p.FMATERIALID AS materialid,",
            "    m.FNUMBER AS code,",
            "    ROUND((p.TotalFACTUALQTY + COALESCE(mis.TotalFACTUALQTY, 0) - COALESCE(r.TotalFACTUALQTY, 0)) / 2, 0) AS qty",
            "FROM ",
            "    PickMtrlData p",
            "LEFT JOIN ",
            "     MisDeliveryData mis ON p.FMATERIALID = mis.FMATERIALID AND p.FWORKSHOPID = mis.FWORKSHOPID",
            "LEFT JOIN ",
            "    ReturnMtrlData r ON p.FMATERIALID = r.FMATERIALID AND p.FWORKSHOPID = r.FWORKSHOPID",
            "LEFT JOIN ",
            "    t_bd_material m ON p.FMATERIALID = m.FMATERIALID",

            "]]>",
            "</script>"
    })
    List<MonthUse> selectMonthUseData();

}
